﻿using System;
using System.Collections.Generic;
using System.Text;
using SocialIn.Core;
using System.Data;
using SocialIn.Core.Data;
using System.Configuration;

namespace SocialIn.Biz
{
    public sealed class Volunteer
    {
        ConnectionStringSettings _BaseDBCon = null;

        public Volunteer()
        {
            _BaseDBCon = ConfigurationManager.ConnectionStrings[ConfigName.DBConfig];
        }

        public Result<DataTable> SearchVolunteer(int pageSize, int currentIndex
            , string location, string talent)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@"declare @index int
declare @pageSize int

set @pageSize = {0}
set @index = {1}

select tempItems.*
from 
(
	select row_number() over(order by a.UserName asc) as rownum
	, a.UserID, a.UserName, a.Email
	, d.Location, a.SelfIntroduction
	from dbo.Member a
		left join dbo.MyActivityLocation d
			on a.UserID = d.UserID
				and d.Preferred = 1
        INNER JOIN MembersInRoles AS B 
            ON A.USERID = B.USERID 
            and B.ROLEID = 'R003' 
	where a.Seceded = 0
		{2}
		{3}
) tempItems
where tempItems.rownum 
		between ((@index - 1) * (@pageSize) + 1) 
		and (@index * (@pageSize))", pageSize, currentIndex
                , String.IsNullOrEmpty(location) ? "" : " and d.Location like '%" + location + "%' "
                , String.IsNullOrEmpty(talent) ? "" : " and b.TalentID = '" + talent + "'");

            //Dictionary<string, string> parameters = new Dictionary<string, string>();

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;           
        }

        public Result<DataTable> SearchMyTalent()
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@"select b.UserID, b.TalentID , c.CodeName as TalentName, c.Etc1 as ImagePath, c.Etc2 as [Desc]
from dbo.MyTalent b	
inner join dbo.CodeMaster c
	on c.Category = 'M003'
		and c.subcategory = b.talentid
where b.Deleted = 0
order by c.Sort");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }

        public int GetVolunteerTotalCount(int pageSize, string location, string talent)
        {
            string query = String.Format(@"select count(*)
	from dbo.Member a
		inner join dbo.MyTalent b
			on a.UserID = b.UserID
				and b.Deleted = 0
		inner join dbo.CodeMaster c
			on c.Category = 'M003'
				and c.subcategory = b.talentid
		left join dbo.MyActivityLocation d
			on a.UserID = d.UserID
				and d.Preferred = 1
        INNER JOIN MembersInRoles AS e
            ON A.USERID = e.USERID 
        and e.ROLEID = 'R003' 
	where a.Seceded = 0
		{0}
		{1}", location == null ? "" : " and d.Location like '%" + location + "%' "
                , talent == null ? "" : " and b.TalentID = '" + talent + "'");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<object> countResult = sql.ExecuteScalar(query);

            int itemCount = Convert.ToInt32(countResult.ReturnValue);

            int totalPagingCount = itemCount / pageSize;

            if ((itemCount % pageSize) != 0)
                totalPagingCount++;

            return totalPagingCount;
        }
    }
}
